CREATE OR REPLACE EDITIONABLE PACKAGE BODY "SCOTT"."PKG_WEEK" is

  ------------------------------------------- calculate the basic data such as open price, close price and so on -------------------------
  PROCEDURE WRITE_STOCK_WEEK as
    -- 表示某只股票的CODE_字段
    v_code varchar2(10);
    -- 某只股票的第一条记录的日期之后的下一个星期一的日期
    v_first_next_monday date;
    -- 定义一个含有5个stock_transaction_data记录的数组
    type type_array is varray(6360) of stock_transaction_data%rowtype;
    array_stock_week_record type_array := type_array();
    -- 表示一个星期中的开盘价，收盘价，最高价和最低价
    v_stock_week_open_price    number;
    v_stock_week_close_price   number;
    v_stock_week_highest_price number;
    v_stock_week_lowest_price  number;
    -- 表示这个星期的交易日数
    v_day_num_in_week number;
    -- 用来重置序列seq_id
    v_seq_num number;
    -- 表示这个星期这只股票的总成交量
    v_week_volume number;
    -- 表示这个星期这只股票的总成交额
    v_week_turnover number;
    -- 返回STOCK_TRANSACTION_DATA表中的code_字段（不包括重复的）
    cursor cur_all_stock_code is
      select distinct std.code_
        from stock_transaction_data std
       order by std.code_ asc;
    -- 根据v_code变量，在表STOCK_TRANSACTION_DATA中查找某只股票的全部记录，并按升序排列
    cursor cur_single_stock_data is
      select *
        from stock_transaction_data std
       where std.code_ = v_code
       order by std.date_ asc;
  begin
    -- 需要先给v_week_volume和v_week_volume赋初值，否则第一支股票的第一个交易周将没有成交量和成交额
    v_week_volume   := 0;
    v_week_turnover := 0;
    for i in cur_all_stock_code loop
      v_code := i.code_;
      -- 重置序列seq_id
      select -seq_id.nextval into v_seq_num from dual;
      execute immediate 'alter sequence seq_id increment by ' || v_seq_num;
      select seq_id.nextval into v_seq_num from dual;
      execute immediate 'alter sequence seq_id increment by 1';
      -- 初始化变量v_first_next_monday。next_day函数的第二个参数为1时表示星期日，为2时才表示星期一。
      select *
        into v_first_next_monday
        from (select next_day(std.date_, 2)
                from stock_transaction_data std
               where std.code_ = v_code
               order by std.date_ asc)
       where rownum <= 1;
      -- 注意：如果不添加这一行，每只股票都会出现开始日期大于结束日期的记录（第一只股票除外）
      array_stock_week_record := type_array();
      for j in cur_single_stock_data loop
        if j.date_ < v_first_next_monday then
          -- 如果是属于同一个星期的记录就保存到数组中
          array_stock_week_record.extend;
          array_stock_week_record(array_stock_week_record.count) := j;
        else
          -- 计算一个星期中的开盘价，收盘价，最高价和最低价
          v_stock_week_open_price  := array_stock_week_record(1).open_price;
          v_stock_week_close_price := array_stock_week_record(array_stock_week_record.count)
                                      .close_price;
          if v_stock_week_open_price > v_stock_week_close_price then
            v_stock_week_highest_price := v_stock_week_open_price;
            v_stock_week_lowest_price  := v_stock_week_close_price;
          end if;
          if v_stock_week_open_price < v_stock_week_close_price then
            v_stock_week_highest_price := v_stock_week_close_price;
            v_stock_week_lowest_price  := v_stock_week_open_price;
          end if;
          for x in 1 .. array_stock_week_record.count loop
            if array_stock_week_record(x)
             .highest_price > v_stock_week_highest_price then
              v_stock_week_highest_price := array_stock_week_record(x)
                                            .highest_price;
            end if;
          end loop;
          for x in 1 .. array_stock_week_record.count loop
            if array_stock_week_record(x)
             .lowest_price < v_stock_week_lowest_price then
              v_stock_week_lowest_price := array_stock_week_record(x)
                                           .lowest_price;
            end if;
          end loop;
          -- 这个星期这只股票的总成交量和成交额
          for x in 1 .. array_stock_week_record.count loop
            v_week_volume   := v_week_volume + array_stock_week_record(x)
                              .volume;
            v_week_turnover := v_week_turnover + array_stock_week_record(x)
                              .turnover;
          end loop;
          -- 插入这个星期的数据
          v_day_num_in_week := array_stock_week_record.count;
          insert into stock_week
            (code_,
             BEGIN_DATE,
             END_DATE,
             NUMBER_,
             OPEN_PRICE,
             CLOSE_PRICE,
             HIGHEST_PRICE,
             LOWEST_PRICE,
             VOLUME,
             TURNOVER)
          values
            (v_code,
             array_stock_week_record   (1).date_,
             array_stock_week_record   (v_day_num_in_week).date_,
             SEQ_ID.NEXTVAL,
             v_stock_week_open_price,
             v_stock_week_close_price,
             v_stock_week_highest_price,
             v_stock_week_lowest_price,
             v_week_volume,
             v_week_turnover);
          -- 为下一轮迭代做准备
          select next_day(j.date_, 2) into v_first_next_monday from dual;
          array_stock_week_record := type_array();
          array_stock_week_record.extend;
          array_stock_week_record(array_stock_week_record.count) := j;
          v_week_volume := 0;
          v_week_turnover := 0;
        end if;
      
      end loop;
    end loop;
  end WRITE_STOCK_week;

  /*--------------------------- 计算周线级别，所有股票的5日均线 ------------------------*/
  procedure WRITE_MA5 is
  begin
    declare
      -- 表示code_
      v_code varchar2(10);
      -- 表示5天收盘价的和
      v_five_sum number := 0;
      -- 表示5天收盘价的平均值
      v_five_average number := 0;
      -- 定义一个含有5个数值型数据的数组
      type type_array is varray(5) of number;
      array_five type_array := type_array();
      -- 返回全部code_
      cursor cur_all_stock_code is
        select distinct t.code_ from stock_week t;
      -- 返回某只股票的收盘价、开始时间、结束时间，并按开始时间升序排列
      cursor cur_all_stock_close_price is
        select t.close_price, t.begin_date, t.end_date
          from stock_week t
         where t.code_ = v_code
         order by t.begin_date asc;
    begin
      for i in cur_all_stock_code loop
        v_code     := i.code_;
        array_five := type_array();
      
        for j in cur_all_stock_close_price loop
          array_five.extend; -- 扩展数组，扩展一个元素
          array_five(array_five.count) := j.close_price;
        
          if mod(array_five.count, 5) = 0 then
            v_five_sum := 0;
          
            for x in 1 .. array_five.count loop
              -- 求5天收盘价的和
              v_five_sum := v_five_sum + array_five(x);
            end loop;
          
            -- 删除数组中的第一个元素，将其与4个元素向前挪一位，并删除下标为5的元素
            for y in 1 .. array_five.count - 1 loop
              array_five(y) := array_five(y + 1);
            end loop;
            array_five.trim;
          
            -- 5天收盘价的平均值
            v_five_average := v_five_sum / 5;
          
            -- 向所有记录的MA5列插入5天收盘价的平均值
            update stock_week t
               set t.ma5 = round(v_five_average, 2)
             where t.code_ = v_code
               and t.begin_date = j.begin_date
               and t.end_date = j.end_date;
          end if;
        end loop;
      end loop;
      commit;
    end;
  end WRITE_MA5;

  /*--------------------------- 计算周线级别，所有股票的10日均线 ------------------------*/
  procedure WRITE_MA10 is
  begin
    declare
      -- 表示code_
      v_code varchar2(10);
      -- 表示10天收盘价的和
      v_ten_sum number := 0;
      -- 表示10天收盘价的平均值
      v_ten_average number := 0;
      -- 定义一个含有10个数值型数据的数组
      type type_array is varray(10) of number;
      array_ten type_array := type_array();
      -- 返回全部code_
      cursor cur_all_stock_code is
        select distinct t.code_ from stock_week t;
      -- 返回某只股票的收盘价、开始时间、结束时间，并按开始时间升序排列
      cursor cur_all_stock_close_price is
        select t.close_price, t.begin_date, t.end_date
          from stock_week t
         where t.code_ = v_code
         order by t.begin_date asc;
    begin
      for i in cur_all_stock_code loop
        v_code    := i.code_;
        array_ten := type_array();
      
        for j in cur_all_stock_close_price loop
          array_ten.extend; -- 扩展数组，扩展一个元素
          array_ten(array_ten.count) := j.close_price;
        
          if mod(array_ten.count, 10) = 0 then
            v_ten_sum := 0;
          
            for x in 1 .. array_ten.count loop
              -- 求10天收盘价的和
              v_ten_sum := v_ten_sum + array_ten(x);
            end loop;
          
            -- 删除数组中的第一个元素，将其与9个元素向前挪一位，并删除下标为10的元素
            for y in 1 .. array_ten.count - 1 loop
              array_ten(y) := array_ten(y + 1);
            end loop;
            array_ten.trim;
          
            -- 10天收盘价的平均值
            v_ten_average := v_ten_sum / 10;
          
            -- 向所有记录的MA10列插入10天收盘价的平均值
            update stock_week t
               set t.ma10 = round(v_ten_average, 2)
             where t.code_ = v_code
               and t.begin_date = j.begin_date
               and t.end_date = j.end_date;
          end if;
        end loop;
      end loop;
      commit;
    end;
  end WRITE_MA10;

  /*--------------------------- 计算周线级别，所有股票的20日均线 ------------------------*/
  procedure WRITE_MA20 is
  begin
    declare
      -- 表示code_
      v_code varchar2(10);
      -- 表示20天收盘价的和
      v_twenty_sum number := 0;
      -- 表示20天收盘价的平均值
      v_twenty_average number := 0;
      -- 定义一个含有20个数值型数据的数组
      type type_array is varray(20) of number;
      array_twenty type_array := type_array();
      -- 返回全部code_
      cursor cur_all_stock_code is
        select distinct t.code_ from stock_week t;
      -- 返回某只股票的收盘价、开始时间、结束时间，并按开始时间升序排列
      cursor cur_al_sStock_close_price is
        select t.close_price, t.begin_date, t.end_date
          from stock_week t
         where t.code_ = v_code
         order by t.begin_date asc;
    begin
      for i in cur_all_stock_code loop
        v_code       := i.code_;
        array_twenty := type_array();
      
        for j in cur_al_sStock_close_price loop
          array_twenty.extend; -- 扩展数组，扩展一个元素
          array_twenty(array_twenty.count) := j.close_price;
        
          if mod(array_twenty.count, 20) = 0 then
            v_twenty_sum := 0;
          
            for x in 1 .. array_twenty.count loop
              -- 求20天收盘价的和
              v_twenty_sum := v_twenty_sum + array_twenty(x);
            end loop;
          
            -- 删除数组中的第一个元素，将其与19个元素向前挪一位，并删除下标为20的元素
            for y in 1 .. array_twenty.count - 1 loop
              array_twenty(y) := array_twenty(y + 1);
            end loop;
            array_twenty.trim;
          
            -- 20天收盘价的平均值
            v_twenty_average := v_twenty_sum / 20;
          
            -- 向所有记录的MA20列插入20天收盘价的平均值
            update stock_week t
               set t.ma20 = round(v_twenty_average, 2)
             where t.code_ = v_code
               and t.begin_date = j.begin_date
               and t.end_date = j.end_date;
          end if;
        end loop;
      end loop;
      commit;
    end;
  end WRITE_MA20;

  /*--------------------------- 计算周线级别，所有股票的60日均线 ------------------------*/
  procedure WRITE_MA60 is
  begin
    declare
      -- 表示code_
      v_code varchar2(10);
      -- 表示60天收盘价的和
      v_sixty_sum number := 0;
      -- 表示60天收盘价的平均值
      v_sixty_average number := 0;
      -- 定义一个含有60个数值型数据的数组
      type type_array is varray(60) of number;
      array_sixty type_array := type_array();
      -- 返回全部code_
      cursor cur_all_stock_code is
        select distinct t.code_ from stock_week t;
      -- 返回某只股票的收盘价、开始时间、结束时间，并按开始时间升序排列
      cursor cur_all_stock_close_price is
        select t.close_price, t.begin_date, t.end_date
          from stock_week t
         where t.code_ = v_code
         order by t.begin_date asc;
    begin
      for i in cur_all_stock_code loop
        v_code      := i.code_;
        array_sixty := type_array();
      
        for j in cur_all_stock_close_price loop
          array_sixty.extend; -- 扩展数组，扩展一个元素
          array_sixty(array_sixty.count) := j.close_price;
        
          if mod(array_sixty.count, 60) = 0 then
            v_sixty_sum := 0;
            for x in 1 .. array_sixty.count loop
              -- 求60天收盘价的和
              v_sixty_sum := v_sixty_sum + array_sixty(x);
            end loop;
          
            -- 删除数组中的第一个元素，将其与59个元素向前挪一位，并删除下标为60的元素
            for y in 1 .. array_sixty.count - 1 loop
              array_sixty(y) := array_sixty(y + 1);
            end loop;
            array_sixty.trim;
          
            -- 60天收盘价的平均值
            v_sixty_average := v_sixty_sum / 60;
          
            -- 向所有记录的MA60列插入60天收盘价的平均值
            update stock_week t
               set t.ma60 = round(v_sixty_average, 2)
             where t.code_ = v_code
               and t.begin_date = j.begin_date
               and t.end_date = j.end_date;
          end if;
        end loop;
      end loop;
      commit;
    end;
  end WRITE_MA60;

  /*--------------------------- 计算周线级别，所有股票的120日均线 ------------------------*/
  procedure WRITE_MA120 is
  begin
    declare
      -- 表示code_
      v_code varchar2(10);
      -- 表示120天收盘价的和
      v_one_hundred_twenty_sum number := 0;
      -- 表示120天收盘价的平均值
      v_one_hundred_twenty_average number := 0;
      -- 定义一个含有120个数值型数据的数组
      type type_array is varray(120) of number;
      array_one_hundred_twenty type_array := type_array();
      -- 返回全部code_
      cursor cur_all_stock_code is
        select distinct t.code_ from stock_week t;
      -- 返回某只股票的收盘价、开始时间、结束时间，并按开始时间升序排列
      cursor cur_all_stock_close_price is
        select t.close_price, t.begin_date, t.end_date
          from stock_week t
         where t.code_ = v_code
         order by t.begin_date asc;
    begin
      for i in cur_all_stock_code loop
        v_code                   := i.code_;
        array_one_hundred_twenty := type_array();
      
        for j in cur_all_stock_close_price loop
          array_one_hundred_twenty.extend; -- 扩展数组，扩展一个元素
          array_one_hundred_twenty(array_one_hundred_twenty.count) := j.close_price;
        
          if mod(array_one_hundred_twenty.count, 120) = 0 then
            v_one_hundred_twenty_sum := 0;
          
            for x in 1 .. array_one_hundred_twenty.count loop
              -- 求120天收盘价的和
              v_one_hundred_twenty_sum := v_one_hundred_twenty_sum +
                                          array_one_hundred_twenty(x);
            end loop;
          
            -- 删除数组中的第一个元素，将其与119个元素向前挪一位，并删除下标为120的元素
            for y in 1 .. array_one_hundred_twenty.count - 1 loop
              array_one_hundred_twenty(y) := array_one_hundred_twenty(y + 1);
            end loop;
            array_one_hundred_twenty.trim;
          
            -- 120天收盘价的平均值
            v_one_hundred_twenty_average := v_one_hundred_twenty_sum / 120;
          
            -- 向所有记录的MA120列插入120天收盘价的平均值
            update stock_week t
               set t.ma120 = round(v_one_hundred_twenty_average, 2)
             where t.code_ = v_code
               and t.begin_date = j.begin_date
               and t.end_date = j.end_date;
          end if;
        end loop;
      end loop;
      commit;
    end;
  end WRITE_MA120;

  /*--------------------------- 计算周线级别，所有股票的250日均线 ------------------------*/
  procedure WRITE_MA250 is
  begin
    declare
      -- 表示code_
      v_code varchar2(10);
      -- 表示250天收盘价的和
      v_one_hundred_fifty_sum number := 0;
      -- 表示250天收盘价的平均值
      v_one_hundred_fifty_average number := 0;
      -- 定义一个含有250个数值型数据的数组
      type type_array is varray(250) of number;
      array_one_hundred_fifty type_array := type_array();
      -- 返回全部code_
      cursor cur_all_stock_code is
        select distinct t.code_ from stock_week t;
      -- 返回某只股票的收盘价、开始时间、结束时间，并按开始时间升序排列
      cursor cur_all_stock_close_price is
        select t.close_price, t.begin_date, t.end_date
          from stock_week t
         where t.code_ = v_code
         order by t.begin_date asc;
    begin
      for i in cur_all_stock_code loop
        v_code                  := i.code_;
        array_one_hundred_fifty := type_array();
      
        for j in cur_all_stock_close_price loop
          array_one_hundred_fifty.extend; -- 扩展数组，扩展一个元素
          array_one_hundred_fifty(array_one_hundred_fifty.count) := j.close_price;
        
          if mod(array_one_hundred_fifty.count, 250) = 0 then
          
            v_one_hundred_fifty_sum := 0;
            for x in 1 .. array_one_hundred_fifty.count loop
              -- 求250天收盘价的和
              v_one_hundred_fifty_sum := v_one_hundred_fifty_sum +
                                         array_one_hundred_fifty(x);
            end loop;
          
            -- 删除数组中的第一个元素，将其与249个元素向前挪一位，并删除下标为250的元素
            for y in 1 .. array_one_hundred_fifty.count - 1 loop
              array_one_hundred_fifty(y) := array_one_hundred_fifty(y + 1);
            end loop;
            array_one_hundred_fifty.trim;
          
            -- 250天收盘价的平均值
            v_one_hundred_fifty_average := v_one_hundred_fifty_sum / 250;
          
            -- 向所有记录的FIVE列插入250天收盘价的平均值
            update stock_week t
               set t.ma250 = round(v_one_hundred_fifty_average, 2)
             where t.code_ = v_code
               and t.begin_date = j.begin_date
               and t.end_date = j.end_date;
          end if;
        end loop;
      end loop;
      commit;
    end;
  end WRITE_MA250;

  /*------------------------ 计算某一周，所有股票的简单移动平均线 ---------------------*/
  procedure WRITE_MA_BY_DATE(p_current_week_begin_date in varchar2,
                             p_current_week_end_date   in varchar2) is
    -- 表示stock_code
    v_code varchar2(10);
    -- 表示5天收盘价的平均值
    v_five_average number := 0;
    -- 表示10天收盘价的平均值
    v_ten_average number := 0;
    -- 表示20天收盘价的平均值
    v_twenty_average number := 0;
    -- 表示60天收盘价的平均值
    v_sixty_average number := 0;
    -- 表示120天收盘价的平均值
    v_one_hundred_twenty_average number := 0;
    -- 表示250天收盘价的平均值
    v_two_hundred_fifty_average number := 0;
    -- 表示所有天收盘价的平均值
    --infiniteAverage number:=0;
    -- 用于判断某只股票的记录数是否可以计算均线
    v_average_num number;
    -- 开始日期
    v_begin_date date;
    -- 结束日期
    v_end_date date;
    -- 计算某只股票，在某个交易日之前的所有交易记录
    cursor cur_stock_row_num_by_date is
      select t.code_, count(*) row_num
        from stock_week t
       where t.end_date < to_date(p_current_week_begin_date, 'yyyy-mm-dd')
       group by t.code_;
  begin
    for i in cur_stock_row_num_by_date loop
      v_code        := i.code_;
      v_average_num := i.row_num;
    
      -- 查找某只股票的开始日期和结束日期
      begin
        select t.begin_date, t.end_date
          into v_begin_date, v_end_date
          from stock_week t
         where t.code_ = v_code
           and t.begin_date between
               to_date(p_current_week_begin_date, 'yyyy-mm-dd') and
               to_date(p_current_week_end_date, 'yyyy-mm-dd')
           and t.end_date between
               to_date(p_current_week_begin_date, 'yyyy-mm-dd') and
               to_date(p_current_week_end_date, 'yyyy-mm-dd');
      exception
        when no_data_found then
          DBMS_OUTPUT.put_line('代码为【' || v_code || '】的股票，
            在日期【' ||
                               p_current_week_begin_date || '】和【' ||
                               p_current_week_end_date || '】之间没有数据');
          continue;
      end;
    
      /*select
             (select avg(stock_close) from(select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=5 order by t.stock_date desc)),
             (select avg(stock_close) from(select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=10 order by t.stock_date desc)),
             (select avg(stock_close) from(select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=20 order by t.stock_date desc)),
             (select avg(stock_close) from(select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=60 order by t.stock_date desc)),
             (select avg(stock_close) from(select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=120 order by t.stock_date desc)),
             (select avg(stock_close) from(select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=250 order by t.stock_date desc))
             into fiveAverage,tenAverage,twentyAverage,sixtyAverage,oneHundredTwentyAverage,twoHundredFiftyAverage
      from dual;
      update stock_moving_average t
      set t.five=round(fiveAverage,2),t.ten=round(tenAverage,2),t.twenty=round(twentyAverage,2),
          t.sixty=round(sixtyAverage,2),t.one_hundred_twenty=round(oneHundredTwentyAverage,2),
          t.two_hundred_fifty=round(twoHundredFiftyAverage,2)
      where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
      commit;*/
    
      -- 每只股票都重置如下变量
      v_five_average               := null;
      v_ten_average                := null;
      v_twenty_average             := null;
      v_sixty_average              := null;
      v_one_hundred_twenty_average := null;
      v_two_hundred_fifty_average  := null;
    
      -- 更新所有股票某一天的5日均线
      if v_average_num >= 5 then
        select avg(d.close_price)
          into v_five_average
          from (select *
                  from stock_week t
                 where t.code_ = v_code
                   and t.end_date <=
                       to_date(p_current_week_end_date, 'yyyy-mm-dd')
                 order by t.begin_date desc) d
         where rownum <= 5;
      end if;
    
      -- 更新所有股票某一天的10日均线
      if v_average_num >= 10 then
        select avg(d.close_price)
          into v_ten_average
          from (select *
                  from stock_week t
                 where t.code_ = v_code
                   and t.end_date <=
                       to_date(p_current_week_end_date, 'yyyy-mm-dd')
                 order by t.begin_date desc) d
         where rownum <= 10;
      end if;
    
      -- 更新所有股票某一天的20日均线
      if v_average_num >= 20 then
        select avg(d.close_price)
          into v_twenty_average
          from (select *
                  from stock_week t
                 where t.code_ = v_code
                   and t.end_date <=
                       to_date(p_current_week_end_date, 'yyyy-mm-dd')
                 order by t.begin_date desc) d
         where rownum <= 20;
      end if;
    
      -- 更新所有股票某一天的60日均线
      if v_average_num >= 60 then
        select avg(d.close_price)
          into v_sixty_average
          from (select *
                  from stock_week t
                 where t.code_ = v_code
                   and t.end_date <=
                       to_date(p_current_week_end_date, 'yyyy-mm-dd')
                 order by t.begin_date desc) d
         where rownum <= 60;
      end if;
    
      -- 更新所有股票某一天的120日均线
      if v_average_num >= 120 then
        select avg(d.close_price)
          into v_one_hundred_twenty_average
          from (select *
                  from stock_week t
                 where t.code_ = v_code
                   and t.end_date <=
                       to_date(p_current_week_end_date, 'yyyy-mm-dd')
                 order by t.begin_date desc) d
         where rownum <= 120;
      end if;
    
      -- 更新所有股票某一天的250日均线
      if v_average_num >= 250 then
        select avg(d.close_price)
          into v_two_hundred_fifty_average
          from (select *
                  from stock_week t
                 where t.code_ = v_code
                   and t.end_date <=
                       to_date(p_current_week_end_date, 'yyyy-mm-dd')
                 order by t.begin_date desc) d
         where rownum <= 250;
      end if;
    
      update stock_week t
         set t.ma5   = nvl2(v_five_average, round(v_five_average, 2), null),
             t.ma10  = nvl2(v_ten_average, round(v_ten_average, 2), null),
             t.ma20  = nvl2(v_twenty_average,
                            round(v_twenty_average, 2),
                            null),
             t.ma60  = nvl2(v_sixty_average, round(v_sixty_average, 2), null),
             t.ma120 = nvl2(v_one_hundred_twenty_average,
                            round(v_one_hundred_twenty_average, 2),
                            null),
             t.ma250 = nvl2(v_two_hundred_fifty_average,
                            round(v_two_hundred_fifty_average, 2),
                            null)
       where t.code_ = v_code
         and t.begin_date = v_begin_date
         and t.end_date = v_end_date;
    
    -- 更新所有股票某一天的所有日均线
    /*select avg(d.stock_close) into infiniteAverage from(
                                                               select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') order by t.stock_date desc
                                                        ) d;
                                                        update stock_moving_average t set t.infinite=round(infiniteAverage,2) where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
                                                        */
    end loop;
    commit;
  end WRITE_MA_BY_DATE;

  -------------------------------------------------------------- calculate KD --------------------------------------------------
  procedure WRITE_WEEK_KD_INIT as
    -- 表示某只股票的STOCK_CODE字段
    v_code varchar2(10);
    -- 用于计算是否是第9个交易周
    num number;
    -- define cursor section.返回全部stock_code
    cursor cur_all_stock_code is
      select distinct t.code_ from stock_week t order by t.code_ asc;
    -- 查询某只股票最初的8个交易周的记录，并按生序排列
    cursor cur_single_stock_k is
      select *
        from (select *
                from stock_week t
               where t.code_ = v_code
               order by t.end_date asc)
       where rownum <= 8;
  begin
    -- 初始化每只股票第一个交易周的K和D字段
    for i in cur_all_stock_code loop
      v_code := i.code_;
      num    := 0;
      for j in cur_single_stock_k loop
        num := num + 1;
        if num = 8 then
          -- 若无前一日K 值与D值，则可分别用50来代替
          update stock_week t
             set t.k = 50, t.d = 50
           where t.code_ = v_code
             and t.end_date = j.end_date;
          commit;
        end if;
      end loop;
    end loop;
  end WRITE_WEEK_KD_INIT;

  procedure WRITE_WEEK_KD_RSV as
    -- 表示某只股票的STOCK_CODE字段
    v_code varchar2(10);
    -- 9日内最高价
    v_nine_day_highest_price number;
    -- 9日内最低价
    v_nine_day_lowest_price number;
    -- 计算指标时使用，用于表示日期的累积
    num number;
    -- define cursor section.返回全部stock_code
    cursor cur_all_stock_code is
      select distinct t.code_ from stock_week t order by t.code_ asc;
    -- 获取某只股票所有的周线级别的交易记录，并按升序排列
    cursor cur_single_stock is
      select *
        from stock_week t
       where t.code_ = v_code
       order by t.end_date asc;
  begin
    for i in cur_all_stock_code loop
      v_code := i.code_;
      num    := 0;
      for j in cur_single_stock loop
        num := num + 1;
        if num >= 9 then
          -- 计算9日内最高价和最低价
          select max(highest_price), min(lowest_price)
            into v_nine_day_highest_price, v_nine_day_lowest_price
            from (select *
                    from stock_week t
                   where t.code_ = v_code
                     and t.end_date <= j.end_date
                   order by t.end_date desc)
           where rownum <= 9;
          -- 计算rsv
          update stock_week t
             set t.rsv =
                 (t.close_price - v_nine_day_lowest_price) /
                 (v_nine_day_highest_price - v_nine_day_lowest_price) * 100
           where t.code_ = v_code
             and t.end_date = j.end_date;
          commit;
        end if;
      end loop;
    end loop;
  end WRITE_WEEK_KD_RSV;

  procedure WRITE_WEEK_KD_K as
    -- 表示某只股票的STOCK_CODE字段
    v_code varchar2(10);
    -- 计算指标时使用，用于表示日期的累积
    num number;
    -- 表示KD指标的K
    v_temp_k number;
    -- define cursor section.返回全部stock_code
    cursor cur_all_stock_code is
      select distinct t.code_ from stock_week t order by t.code_ asc;
    -- 获取某只股票所有的周线级别的交易记录，并按升序排列
    cursor cur_single_stock is
      select *
        from stock_week t
       where t.code_ = v_code
       order by t.end_date asc;
  begin
    for i in cur_all_stock_code loop
      v_code := i.code_;
      num    := 0;
      for j in cur_single_stock loop
        num := num + 1;
        if num = 8 then
          v_temp_k := j.k;
        end if;
        if num >= 9 then
          -- 计算K
          update stock_week t
             set t.k = 2 / 3 * v_temp_k + 1 / 3 * t.rsv
           where t.code_ = v_code
             and t.end_date = j.end_date;
          commit;
          select t.k
            into v_temp_k
            from stock_week t
           where t.code_ = v_code
             and t.end_date = j.end_date;
        end if;
      
      end loop;
    end loop;
  end WRITE_WEEK_KD_K;

  procedure WRITE_WEEK_KD_D as
    -- 表示某只股票的STOCK_CODE字段
    v_code varchar2(10);
    -- 计算指标时使用，用于表示日期的累积
    num number;
    -- 表示KD指标的D
    v_temp_d number;
    -- define cursor section.返回全部stock_code
    cursor cur_all_stock_code is
      select distinct t.code_ from stock_week t order by t.code_ asc;
    -- 获取某只股票所有的周线级别的交易记录，并按升序排列
    cursor cur_single_stock is
      select *
        from stock_week t
       where t.code_ = v_code
       order by t.end_date asc;
  begin
    for i in cur_all_stock_code loop
      v_code := i.code_;
      num    := 0;
      for j in cur_single_stock loop
        num := num + 1;
        if num = 8 then
          v_temp_d := j.d;
        end if;
        if num >= 9 then
          -- 计算D
          update stock_week t
             set t.d = 2 / 3 * v_temp_d + 1 / 3 * j.k
           where t.code_ = v_code
             and t.end_date = j.end_date;
          commit;
          select t.d
            into v_temp_d
            from stock_week t
           where t.code_ = v_code
             and t.end_date = j.end_date;
        end if;
      end loop;
    end loop;
  end WRITE_WEEK_KD_D;

  --------------------- calculate up_down -----------------------------
  procedure WRITE_WEEK_UP_DOWN as
    -- 表示某只股票的CODE_字段
    v_code varchar2(10);
    -- 表示前一周的close_price
    v_pre_close_price number;
    -- 表示某只股票第一周的close_price
    v_init_close_price number;
    -- define cursor section.返回全部code_
    cursor cur_all_stock_code is
      select distinct t.code_ from stock_week t order by t.code_ asc;
    -- 返回某只股票所有交易周的收盘价
    cursor cur_single_week_close is
      select *
        from stock_week t
       where t.code_ = v_code
       order by t.end_date asc;
  begin
    for i in cur_all_stock_code loop
      v_code := i.code_;
      select *
        into v_init_close_price
        from (select t.close_price
                from stock_week t
               where t.code_ = v_code
               order by t.end_date asc)
       where rownum <= 1;
      v_pre_close_price := v_init_close_price;
      for j in cur_single_week_close loop
        if v_pre_close_price = j.close_price then
          update stock_week t
             set t.up_down = 0
           where t.code_ = j.code_
             and t.begin_date = j.begin_date
             and t.end_date = j.end_date;
        elsif v_pre_close_price < j.close_price then
          update stock_week t
             set t.up_down = 1
           where t.code_ = j.code_
             and t.begin_date = j.begin_date
             and t.end_date = j.end_date;
        else
          update stock_week t
             set t.up_down = -1
           where t.code_ = j.code_
             and t.begin_date = j.begin_date
             and t.end_date = j.end_date;
        end if;
        v_pre_close_price := j.close_price;
      end loop;
    end loop;
  end WRITE_WEEK_UP_DOWN;

  /*------------------------------- judge whether the close of stock is up or down according the given weekend -------------------------------*/
  procedure WRITE_WEEK_UP_DOWN_BY_DATE(p_begin_date in varchar2,
                                       p_end_date   in varchar2) as
    -- 表示stock_code
    v_code varchar2(10);
    -- 表示当前股票某一周的收盘价
    v_current_close_price number;
    -- 表示当前股票某一日的日期
    v_current_end_date Date;
    -- define cursor section.返回全部stock_code。
    cursor cur_all_stock_code is
      select distinct t.code_ from stock_week t order by t.code_ asc;
    -- 返回某只股票从某一日开始最近两天的交易记录
    cursor cur_two_stock is
      select *
        from (select *
                from stock_week t
               where t.code_ = v_code
                 and t.begin_date <= to_date(p_begin_date, 'yyyy-mm-dd')
                 and t.end_date <= to_date(p_end_date, 'yyyy-mm-dd')
               order by t.end_date desc)
       where rownum <= 2;
  begin
    for i in cur_all_stock_code loop
      v_code := i.code_;
      -- 返回某只股票某一日的收盘价和日期
      select t.close_price, t.end_date
        into v_current_close_price, v_current_end_date
        from stock_week t
       where t.code_ = v_code
         and t.begin_date <= to_date(p_begin_date, 'yyyy-mm-dd')
         and t.end_date <= to_date(p_end_date, 'yyyy-mm-dd')
         and rownum <= 1
       order by t.end_date desc;
      for j in cur_two_stock loop
        if v_current_end_date != j.end_date then
          if j.close_price > v_current_close_price then
            update stock_week t
               set t.up_down = -1
             where t.code_ = i.code_
               and t.begin_date = to_date(p_begin_date, 'yyyy-mm-dd')
               and t.end_date = to_date(p_end_date, 'yyyy-mm-dd');
          elsif j.close_price < v_current_close_price then
            update stock_week t
               set t.up_down = 1
             where t.code_ = i.code_
               and t.begin_date = to_date(p_begin_date, 'yyyy-mm-dd')
               and t.end_date = to_date(p_end_date, 'yyyy-mm-dd');
          else
            update stock_week t
               set t.up_down = 0
             where t.code_ = i.code_
               and t.begin_date = to_date(p_begin_date, 'yyyy-mm-dd')
               and t.end_date = to_date(p_end_date, 'yyyy-mm-dd');
          end if;
        end if;
      end loop;
    end loop;
  end WRITE_WEEK_UP_DOWN_BY_DATE;

  --------------------- calculate the basic data such as open price, close price and so on by date -----------------------------
  procedure WRITE_STOCK_WEEK_BY_DATE(p_begin_date varchar2,
                                     p_end_date   varchar2) as
    -- 表示某只股票的CODE_字段
    v_code varchar2(10);
    -- 分别代表某只股票在某个星期的最高价，最低价，开盘价，收盘价，成交量和序列号
    v_highest_price number;
    v_lowest_price  number;
    v_open_price    number;
    v_close_price   number;
    v_sum_volume    number;
    v_sum_turnover  number;
    v_stock_seq_id  number;
    v_num           number;
    -- define cursor section.返回全部CODE_
    cursor cur_all_stock_code is
      select distinct t.code_
        from stock_transaction_data t
       order by t.code_ asc;
  begin
    for i in cur_all_stock_code loop
      v_code := i.code_;
      select count(*)
        into v_num
        from stock_transaction_data t
       where t.code_ = v_code
         and t.date_ between to_date(p_begin_date, 'yyyy-mm-dd') and
             to_date(p_end_date, 'yyyy-mm-dd');
      if v_num != 0 then
        -- dbms_output.put_line(v_code);
        -- 计算某只股票在某个星期的最高价，最低价，开盘价，收盘价，成交量和成交额
        select max(t.highest_price), min(t.lowest_price)
          into v_highest_price, v_lowest_price
          from stock_transaction_data t
         where t.code_ = v_code
           and t.date_ between to_date(p_begin_date, 'yyyy-mm-dd') and
               to_date(p_end_date, 'yyyy-mm-dd')
         order by t.date_ asc;
        select *
          into v_open_price
          from (select t.open_price
                  from stock_transaction_data t
                 where t.code_ = v_code
                   and t.date_ between to_date(p_begin_date, 'yyyy-mm-dd') and
                       to_date(p_end_date, 'yyyy-mm-dd')
                 order by t.date_ asc)
         where rownum <= 1;
        select *
          into v_close_price
          from (select t.close_price
                  from stock_transaction_data t
                 where t.code_ = v_code
                   and t.date_ between to_date(p_begin_date, 'yyyy-mm-dd') and
                       to_date(p_end_date, 'yyyy-mm-dd')
                 order by t.date_ desc)
         where rownum <= 1;
        select sum(t.volume)
          into v_sum_volume
          from stock_transaction_data t
         where t.code_ = v_code
           and t.date_ between to_date(p_begin_date, 'yyyy-mm-dd') and
               to_date(p_end_date, 'yyyy-mm-dd');
        select sum(t.turnover)
          into v_sum_turnover
          from stock_transaction_data t
         where t.code_ = v_code
           and t.date_ between to_date(p_begin_date, 'yyyy-mm-dd') and
               to_date(p_end_date, 'yyyy-mm-dd');
        select max(t.number_)
          into v_stock_seq_id
          from stock_week t
         where t.code_ = v_code
         order by t.end_date desc;
      
        -- 如果是一只新股，则在表stock_moving_average有记录，但是在表stock_weekend中就没有记录。因此应当初始化stockSeqId为0，否则stockSeqId为null，会报错
        if v_stock_seq_id is null then
          v_stock_seq_id := 0;
        end if;
      
        insert into stock_week
          (code_,
           begin_date,
           end_date,
           number_,
           highest_price,
           lowest_price,
           open_price,
           close_price,
           volume,
           turnover)
        values
          (v_code,
           to_date(p_begin_date, 'yyyy-mm-dd'),
           to_date(p_end_date, 'yyyy-mm-dd'),
           v_stock_seq_id + 1,
           v_highest_price,
           v_lowest_price,
           v_open_price,
           v_close_price,
           v_sum_volume,
           v_sum_turnover);
        commit;
      end if;
    end loop;
  end WRITE_STOCK_WEEK_BY_DATE;

  ---------------------------------------------------------- calculate KD by date -----------------------------------------------
  procedure WRITE_WEEK_KD_BY_DATE_RSV(p_begin_date varchar2,
                                      p_end_date   varchar2) as
    -- 表示某只股票的STOCK_CODE字段
    v_code varchar2(10);
    -- 9日内最高价
    v_nine_day_highest_price number;
    -- 9日内最低价
    v_nine_day_lowest_price number;
    -- define cursor section.返回全部stock_code
    cursor cur_all_stock_code is
      select distinct t.code_ from stock_week t order by t.code_ asc;
  begin
    for i in cur_all_stock_code loop
      v_code := i.code_;
      -- 计算9日内最高价和最低价
      select max(highest_price), min(lowest_price)
        into v_nine_day_highest_price, v_nine_day_lowest_price
        from (select *
                from stock_week t
               where t.code_ = v_code
                 and t.begin_date <= to_date(p_begin_date, 'yyyy-mm-dd')
                 and t.end_date <= to_date(p_end_date, 'yyyy-mm-dd')
               order by t.end_date desc)
       where rownum <= 9;
      -- 计算某只股票某一周的RSV
      update stock_week t
         set t.rsv =
             (t.close_price - v_nine_day_lowest_price) /
             (v_nine_day_highest_price - v_nine_day_lowest_price) * 100
       where t.code_ = v_code
         and t.begin_date <= to_date(p_begin_date, 'yyyy-mm-dd')
         and t.end_date <= to_date(p_end_date, 'yyyy-mm-dd');
      commit;
    end loop;
  end WRITE_WEEK_KD_BY_DATE_RSV;

  procedure WRITE_WEEK_KD_BY_DATE_K(p_begin_date varchar2,
                                    p_end_date   varchar2) as
    -- 表示某只股票的STOCK_CODE字段
    v_code varchar2(10);
    -- 表示前一日K值
    v_temp_k number;
    -- 用于计数
    v_num number;
    -- define cursor section.返回全部stock_code
    cursor cur_all_stock_code is
      select distinct t.code_ from stock_week t order by t.code_ asc;
    -- 按照日期参数，获取某只股票最近两天的周线级别的交易记录，并按降序排列
    cursor cur_single_stock is
      select *
        from (select *
                from stock_week t
               where t.code_ = v_code
                 and t.begin_date <= to_date(p_begin_date, 'yyyy-mm-dd')
                 and t.end_date <= to_date(p_end_date, 'yyyy-mm-dd')
               order by t.begin_date desc)
       where rownum <= 2
       order by begin_date asc;
  begin
    for i in cur_all_stock_code loop
      v_code := i.code_;
      v_num  := 0;
      for j in cur_single_stock loop
        v_num := v_num + 1;
        if v_num = 1 then
          v_temp_k := j.k;
        end if;
        if v_num = 2 then
          update stock_week t
             set t.k = 2 / 3 * v_temp_k + 1 / 3 * t.rsv
           where t.code_ = v_code
             and t.begin_date = to_date(p_begin_date, 'yyyy-mm-dd')
             and t.end_date = to_date(p_end_date, 'yyyy-mm-dd');
          commit;
        end if;
      end loop;
    end loop;
  end WRITE_WEEK_KD_BY_DATE_K;

  procedure WRITE_WEEK_KD_BY_DATE_D(p_begin_date varchar2,
                                    p_end_date   varchar2) as
    -- 表示某只股票的STOCK_CODE字段
    v_code varchar2(10);
    -- 表示前一日K值
    v_temp_d number;
    -- 用于计数
    v_num number;
    -- define cursor section.返回全部stock_code
    cursor cur_all_stock_code is
      select distinct t.code_ from stock_week t order by t.code_ asc;
    -- 按照日期参数，获取某只股票最近两天的周线级别的交易记录，并按降序排列
    cursor cur_single_stock is
      select *
        from (select *
                from stock_week t
               where t.code_ = v_code
                 and t.begin_date <= to_date(p_begin_date, 'yyyy-mm-dd')
                 and t.end_date <= to_date(p_end_date, 'yyyy-mm-dd')
               order by t.begin_date desc)
       where rownum <= 2
       order by begin_date asc;
  begin
    for i in cur_all_stock_code loop
      v_code := i.code_;
      v_num  := 0;
      for j in cur_single_stock loop
        v_num := v_num + 1;
        if v_num = 1 then
          v_temp_d := j.d;
        end if;
        if v_num = 2 then
          update stock_week t
             set t.d = 2 / 3 * v_temp_d + 1 / 3 * t.k
           where t.code_ = v_code
             and t.begin_date = to_date(p_begin_date, 'yyyy-mm-dd')
             and t.end_date = to_date(p_end_date, 'yyyy-mm-dd');
          commit;
        end if;
      end loop;
    end loop;
  end WRITE_WEEK_KD_BY_DATE_D;

  /*--------------------------------------------------- write weekend MACD of all stocks ---------------------------------------------------*/
  procedure WRITE_WEEK_MACD_INIT as
    -- 表示stock_code
    v_stock_week_code varchar2(10);
    -- 返回全部stock_code
    cursor cur_all_stock_week_code is
      select distinct t.code_ from stock_week t order by t.code_ asc;
    -- 获取每只股票第一个交易周的日期
    cursor cur_first_stock_week_date is
      select *
        from (select t.begin_date
                from stock_week t
               where t.code_ = v_stock_week_code
               order by t.begin_date asc)
       where rownum <= 1;
  begin
    -- 初始化每只股票第一个交易周的ema12,ema26,dif和dea字段
    for i in cur_all_stock_week_code loop
      v_stock_week_code := i.code_;
      for j in cur_first_stock_week_date loop
        update stock_week t
           set t.ema12 = t.close_price,
               t.ema26 = t.close_price,
               t.dif   = 0,
               t.dea   = 0
         where t.code_ = v_stock_week_code
           and t.begin_date = j.begin_date;
        commit;
      end loop;
    end loop;
  end WRITE_WEEK_MACD_INIT;

  procedure WRITE_WEEK_MACD_EMA as
    v_pre_ema12  number;
    v_pre_ema26  number;
    v_pre_dif    number;
    v_pre_dea    number;
    v_first_date date;
    -- 表示stock_code
    v_stock_week_code varchar2(10);
    -- 返回全部stock_code
    cursor cur_all_stock_week_code is
      select distinct t.code_ from stock_week t order by t.code_ asc;
    -- 获取每只股票第一个交易周的日期
    cursor cur_first_stock_week_date is
      select *
        from (select t.begin_date
                from stock_week t
               where t.code_ = v_stock_week_code
               order by t.begin_date asc)
       where rownum <= 1;
    -- 根据stockCode选出某只股票的除第一周外的全部交易记录，按升序排列
    cursor cur_all_stock_week is
      select distinct *
        from stock_week t
       where t.code_ = v_stock_week_code
         and t.end_date > v_first_date
       order by t.begin_date asc;
  begin
    -- 计算每只股票其余交易周的ema12,ema26,dif和dea字段
    for i in cur_all_stock_week_code loop
      v_stock_week_code := i.code_;
      -- 用记录是第一个交易周的字段初始化相关变量
      for x in cur_first_stock_week_date loop
        select t.ema12, t.ema26, t.dif, t.dea
          into v_pre_ema12, v_pre_ema26, v_pre_dif, v_pre_dea
          from stock_week t
         where t.code_ = v_stock_week_code
           and t.begin_date = x.begin_date;
      end loop;
    
      select *
        into v_first_date
        from (select t.begin_date
                from stock_week t
               where t.code_ = v_stock_week_code
               order by t.begin_date asc)
       where rownum <= 1;
      for j in cur_all_stock_week loop
        -- 对于其余交易周，更新ema12,ema26,dif和dea字段
        update stock_week t
           set t.ema12 = v_pre_ema12 * 11 / 13 + j.close_price * 2 / 13,
               t.ema26 = v_pre_ema26 * 25 / 27 + j.close_price * 2 / 27
         where t.code_ = v_stock_week_code
           and t.begin_date = j.begin_date;
        commit;
        -- 用于计算下一个交易周时使用
        select t.ema12
          into v_pre_ema12
          from stock_week t
         where t.code_ = v_stock_week_code
           and t.begin_date = j.begin_date;
        select t.ema26
          into v_pre_ema26
          from stock_week t
         where t.code_ = v_stock_week_code
           and t.begin_date = j.begin_date;
      end loop;
    end loop;
  end WRITE_WEEK_MACD_EMA;

  procedure WRITE_WEEK_MACD_DIF as
    v_pre_ema12  number;
    v_pre_ema26  number;
    v_pre_dif    number;
    v_pre_dea    number;
    v_first_date date;
    -- 表示stock_code
    v_stock_week_code varchar2(10);
    -- 返回全部stock_code
    cursor cur_all_stock_week_code is
      select distinct t.code_ from stock_week t order by t.code_ asc;
    -- 获取每只股票第一个交易周的日期
    cursor cur_first_stock_week_date is
      select *
        from (select t.begin_date
                from stock_week t
               where t.code_ = v_stock_week_code
               order by t.begin_date asc)
       where rownum <= 1;
    -- 根据stockCode选出某只股票的除第一周以外的全部交易记录，按升序排列
    cursor cur_all_stock_week is
      select distinct *
        from stock_week t
       where t.code_ = v_stock_week_code
         and t.begin_date > v_first_date
       order by t.begin_date asc;
  begin
    -- 计算每只股票其余交易周的ema12,ema26,dif和dea字段
    for i in cur_all_stock_week_code loop
      v_stock_week_code := i.code_;
      -- 用记录是第一个交易周的字段初始化相关变量
      for x in cur_first_stock_week_date loop
        select t.ema12, t.ema26, t.dif, t.dea
          into v_pre_ema12, v_pre_ema26, v_pre_dif, v_pre_dea
          from stock_week t
         where t.code_ = v_stock_week_code
           and t.begin_date = x.begin_date;
      end loop;
      select *
        into v_first_date
        from (select t.begin_date
                from stock_week t
               where t.code_ = v_stock_week_code
               order by t.begin_date asc)
       where rownum <= 1;
      for j in cur_all_stock_week loop
        update stock_week t
           set t.dif = t.ema12 - t.ema26
         where t.code_ = v_stock_week_code
           and t.begin_date = j.begin_date;
        commit;
        select t.dif
          into v_pre_dif
          from stock_week t
         where t.code_ = v_stock_week_code
           and t.begin_date = j.begin_date;
      end loop;
    end loop;
  end WRITE_WEEK_MACD_DIF;

  procedure WRITE_WEEK_MACD_DEA as
    v_pre_ema12  number;
    v_pre_ema26  number;
    v_pre_dif    number;
    v_pre_dea    number;
    v_first_date date;
    -- 表示stock_code
    v_stock_week_code varchar2(10);
    -- 返回全部stock_code
    cursor cur_all_stock_week_code is
      select distinct t.code_ from stock_week t order by t.code_ asc;
    -- 获取每只股票第一个交易周的日期
    cursor cur_first_stock_week_date is
      select *
        from (select t.begin_date
                from stock_week t
               where t.code_ = v_stock_week_code
               order by t.begin_date asc)
       where rownum <= 1;
    -- 根据stockCode选出某只股票的除第一周以外的全部交易记录，按升序排列
    cursor cur_all_stock_week is
      select distinct *
        from stock_week t
       where t.code_ = v_stock_week_code
         and t.begin_date > v_first_date
       order by t.begin_date asc;
  begin
    -- 计算每只股票其余交易周的ema12,ema26,dif和dea字段
    for i in cur_all_stock_week_code loop
      v_stock_week_code := i.code_;
      -- 用记录是第一个交易周的字段初始化相关变量
      for x in cur_first_stock_week_date loop
        select t.ema12, t.ema26, t.dif, t.dea
          into v_pre_ema12, v_pre_ema26, v_pre_dif, v_pre_dea
          from stock_week t
         where t.code_ = v_stock_week_code
           and t.begin_date = x.begin_date;
      end loop;
      select *
        into v_first_date
        from (select t.begin_date
                from stock_week t
               where t.code_ = v_stock_week_code
               order by t.begin_date asc)
       where rownum <= 1;
      for j in cur_all_stock_week loop
        update stock_week t
           set t.dea = v_pre_dea * 8 / 10 + t.dif * 2 / 10
         where t.code_ = v_stock_week_code
           and t.begin_date = j.begin_date;
        commit;
        select t.dea
          into v_pre_dea
          from stock_week t
         where t.code_ = v_stock_week_code
           and t.begin_date = j.begin_date;
      end loop;
    end loop;
  end WRITE_WEEK_MACD_DEA;

  /*-------------------------------- write weekend MACD of all stocks by date -----------------------------------------------*/
  procedure WRITE_WEEK_MACD_EMA_BY_DATE(p_stock_week_begin_date in varchar2,
                                        p_stock_week_end_date   in varchar2) as
    v_pre_ema12 number;
    v_pre_ema26 number;
    v_pre_dea   number;
    -- 表示stock_code
    v_stock_week_code varchar2(10);
    -- 返回全部stock_code
    cursor cur_all_stock_week_code is
      select distinct t.code_ from stock_week t order by t.code_ asc;
    -- 获取某只股票最近两天的记录
    cursor cur_single_stock_week is
      select *
        from (select *
                from (select *
                        from stock_week t
                       where t.code_ = v_stock_week_code
                         and t.begin_date <=
                             to_date(p_stock_week_begin_date, 'yyyy-mm-dd')
                         and t.end_date <=
                             to_date(p_stock_week_end_date, 'yyyy-mm-dd')
                       order by t.begin_date desc)
               where rownum <= 2) o
       order by o.begin_date asc;
  begin
    for i in cur_all_stock_week_code loop
      v_stock_week_code := i.code_;
      for j in cur_single_stock_week loop
        if j.begin_date != to_date(p_stock_week_begin_date, 'yyyy-mm-dd') and
           j.end_date != to_date(p_stock_week_end_date, 'yyyy-mm-dd') then
          v_pre_ema12 := j.ema12;
          v_pre_ema26 := j.ema26;
          v_pre_dea   := j.dea;
        else
          update stock_week t
             set t.ema12 = v_pre_ema12 * 11 / 13 + j.close_price * 2 / 13,
                 t.ema26 = v_pre_ema26 * 25 / 27 + j.close_price * 2 / 27
           where t.code_ = v_stock_week_code
             and t.begin_date =
                 to_date(p_stock_week_begin_date, 'yyyy-mm-dd')
             and t.end_date = to_date(p_stock_week_end_date, 'yyyy-mm-dd');
          commit;
        end if;
      end loop;
    end loop;
  end WRITE_WEEK_MACD_EMA_BY_DATE;

  procedure WRITE_WEEK_MACD_DIF_BY_DATE(p_stock_week_begin_date in varchar2,
                                        p_stock_week_end_date   in varchar2) as
    v_pre_ema12 number;
    v_pre_ema26 number;
    v_pre_dea   number;
    -- 表示stock_code
    v_stock_week_code varchar2(10);
    -- 返回全部stock_code
    cursor cur_all_stock_week_code is
      select distinct t.code_ from stock_week t order by t.code_ asc;
    -- 获取某只股票最近两天的记录
    cursor cur_single_stock_week is
      select *
        from (select *
                from (select *
                        from stock_week t
                       where t.code_ = v_stock_week_code
                         and t.begin_date <=
                             to_date(p_stock_week_begin_date, 'yyyy-mm-dd')
                         and t.end_date <=
                             to_date(p_stock_week_end_date, 'yyyy-mm-dd')
                       order by t.begin_date desc)
               where rownum <= 2) o
       order by o.begin_date asc;
  begin
    for i in cur_all_stock_week_code loop
      v_stock_week_code := i.code_;
      for j in cur_single_stock_week loop
        if j.begin_date != to_date(p_stock_week_begin_date, 'yyyy-mm-dd') and
           j.end_date != to_date(p_stock_week_end_date, 'yyyy-mm-dd') then
          v_pre_ema12 := j.ema12;
          v_pre_ema26 := j.ema26;
          v_pre_dea   := j.dea;
        else
          update stock_week t
             set t.dif = t.ema12 - t.ema26
           where t.code_ = v_stock_week_code
             and t.begin_date =
                 to_date(p_stock_week_begin_date, 'yyyy-mm-dd')
             and t.end_date = to_date(p_stock_week_end_date, 'yyyy-mm-dd');
          commit;
        end if;
      end loop;
    end loop;
  end WRITE_WEEK_MACD_DIF_BY_DATE;

  procedure WRITE_WEEK_MACD_DEA_BY_DATE(p_stock_week_begin_date in varchar2,
                                        p_stock_week_end_date   in varchar2) as
    v_pre_ema12 number;
    v_pre_ema26 number;
    v_pre_dea   number;
    -- 表示stock_code
    v_stock_week_code varchar2(10);
    -- 返回全部stock_code
    cursor cur_all_stock_week_code is
      select distinct t.code_ from stock_week t order by t.code_ asc;
    -- 获取某只股票最近两天的记录
    cursor cur_single_stock_week is
      select *
        from (select *
                from (select *
                        from stock_week t
                       where t.code_ = v_stock_week_code
                         and t.begin_date <=
                             to_date(p_stock_week_begin_date, 'yyyy-mm-dd')
                         and t.end_date <=
                             to_date(p_stock_week_end_date, 'yyyy-mm-dd')
                       order by t.begin_date desc)
               where rownum <= 2) o
       order by o.begin_date asc;
  begin
    for i in cur_all_stock_week_code loop
      v_stock_week_code := i.code_;
      for j in cur_single_stock_week loop
        if j.begin_date != to_date(p_stock_week_begin_date, 'yyyy-mm-dd') and
           j.end_date != to_date(p_stock_week_end_date, 'yyyy-mm-dd') then
          v_pre_ema12 := j.ema12;
          v_pre_ema26 := j.ema26;
          v_pre_dea   := j.dea;
        else
          update stock_week t
             set t.dea = v_pre_dea * 8 / 10 + t.dif * 2 / 10
           where t.code_ = v_stock_week_code
             and t.begin_date =
                 to_date(p_stock_week_begin_date, 'yyyy-mm-dd')
             and t.end_date = to_date(p_stock_week_end_date, 'yyyy-mm-dd');
          commit;
        end if;
      end loop;
    end loop;
  end WRITE_WEEK_MACD_DEA_BY_DATE;

  /*-------------------------------- calculate WRITE_WEEK_CHANGE_RANGE_EX_RIGHT----------------------------------------*/
  procedure WRITE_WEEK_C_R_E_R as
    -- 表示股票代码
    v_code varchar2(10);
    -- 用来记录某只股票的第一条记录
    row_stock_week stock_week%rowtype;
    -- define cursor section.返回全部stock_code
    cursor cur_all_stock_code is
      select distinct t.code_ from stock_week t order by t.code_ asc;
    -- 返回某一只股票除第一条记录以外的其他记录，并按生序排列
    cursor cur_single_stock_other_record is
      select *
        from stock_week t
       where t.code_ = v_code
         and t.begin_date != row_stock_week.begin_date
       order by t.begin_date asc;
  begin
    for i in cur_all_stock_code loop
      v_code := i.code_;
    
      -- 计算某一只股票change_range_ex_right字段的值
      select *
        into row_stock_week
        from (select *
                from stock_week t
               where t.code_ = i.code_
               order by t.begin_date asc)
       where rownum <= 1;
      for j in cur_single_stock_other_record loop
        update stock_week t
           set t.change_range_ex_right = round((j.close_price -
                                               row_stock_week.close_price) /
                                               row_stock_week.close_price,
                                               4) * 100
         where t.code_ = i.code_
           and t.begin_date = j.begin_date;
        row_stock_week.close_price := j.close_price;
        commit;
      end loop;
    end loop;
  end WRITE_WEEK_C_R_E_R;

  /*-------------------------- calculate WRITE_WEEK_CHANGE_RANGE_EX_RIGHT_BY_DATE-------------------------------*/
  procedure WRITE_WEEK_C_R_E_R_BY_DATE(p_stock_week_begin_date in varchar2,
                                       p_stock_week_end_date   in varchar2) as
    -- 表示股票代码
    v_code varchar2(10);
    -- 本周的收盘价
    v_current_close_price number;
    -- 前一周收盘价
    v_last_close_price number;
    -- define cursor section.返回全部stock_code
    cursor cur_all_stock_code is
      select distinct t.code_ from stock_week t order by t.code_ asc;
  begin
    for i in cur_all_stock_code loop
      v_code := i.code_;
      -- 本周的收盘价
      begin
        select t.close_price
          into v_current_close_price
          from stock_week t
         where t.code_ = v_code
           and t.begin_date >=
               to_date(p_stock_week_begin_date, 'yyyy-mm-dd')
           and t.end_date <= to_date(p_stock_week_end_date, 'yyyy-mm-dd');
      
      exception
        when NO_DATA_FOUND then
          dbms_output.put_line('stock_week表中，没有code_为【' || v_code ||
                               '】，begin_date为【' || p_stock_week_begin_date || '】，
                               end_date为【' ||
                               p_stock_week_end_date || '】时的记录');
          continue;
      end;
    
      -- 前一周收盘价
      begin
        select b.close_price
          into v_last_close_price
          from (select *
                  from stock_week t
                 where t.code_ = v_code
                   and t.begin_date <
                       to_date(p_stock_week_begin_date, 'yyyy-mm-dd')
                   and t.end_date <
                       to_date(p_stock_week_end_date, 'yyyy-mm-dd')
                 order by t.begin_date desc) b
         where rownum <= 1;
      
      exception
        when NO_DATA_FOUND then
          dbms_output.put_line('stock_week表中，没有code_为【' || v_code ||
                               '】，begin_date为【' || p_stock_week_begin_date || '】，
                               end_date为【' ||
                               p_stock_week_end_date || '】时的记录');
          continue;
      end;
    
      -- 更新change_range_ex_right字段
      update stock_week
         set change_range_ex_right = round((v_current_close_price -
                                           v_last_close_price) /
                                           v_last_close_price,
                                           4) * 100
       where code_ = i.code_
         and begin_date >= to_date(p_stock_week_begin_date, 'yyyy-mm-dd')
         and end_date <= to_date(p_stock_week_end_date, 'yyyy-mm-dd');
    
    end loop;
    commit;
  end WRITE_WEEK_C_R_E_R_BY_DATE;

  /*--------------------- 计算stock_week表中的布林带 -----------------------*/
  procedure CAL_BOLL is
    -- 股票代码
    v_code varchar(50);
    -- 布林带中轨、上轨、下轨
    v_mb number;
    v_up number;
    v_dn number;
    -- 计算标准差时使用
    v_sum number := 0;
    -- 记录数量
    v_num number;
    -- 某只股票的一条交易记录
    row_stock_week      stock_week%rowtype;
    row_stock_week_desc stock_week%rowtype;
    -- 周线级别所有股票的code_
    cursor cur_stock_week_code is
      select distinct t.code_ from stock_week t;
    -- 某一只股票的全部交易记录，升序排列
    cursor cur_single_stock_week is
      select *
        from stock_week t
       where t.code_ = v_code
         and t.ma20 is not null
       order by t.begin_date asc;
    -- 某只股票，在某日之后的交易记录，降序排列
    cursor cur_stock_week_desc is
      select *
        from (select *
                from stock_week t
               where t.code_ = row_stock_week.code_
                 and t.begin_date <= row_stock_week.begin_date
               order by t.begin_date desc)
       where rownum <= 20;
  begin
    open cur_stock_week_code;
    loop
      fetch cur_stock_week_code
        into v_code;
      exit when cur_stock_week_code%notfound;
    
      open cur_single_stock_week;
      loop
        fetch cur_single_stock_week
          into row_stock_week;
        exit when cur_single_stock_week%notfound;
      
        -- 初始化
        v_up  := 0;
        v_dn  := 0;
        v_sum := 0;
      
        -- 中轨
        select avg(t1.close_price)
          into v_mb
          from (select *
                  from stock_week t
                 where t.code_ = row_stock_week.code_
                   and t.begin_date <= row_stock_week.begin_date
                 order by t.begin_date desc) t1
         where rownum <= 20;
        -- v_mb := row_stock_week.ma20;
      
        -- 如果交易次数不够20，则返回
        select count(*)
          into v_num
          from stock_week t
         where t.code_ = row_stock_week.code_
           and t.begin_date <= row_stock_week.begin_date;
        if v_num < 20 then
          continue;
        end if;
      
        open cur_stock_week_desc;
        loop
          fetch cur_stock_week_desc
            into row_stock_week_desc;
          exit when cur_stock_week_desc%notfound;
          v_sum := v_sum + power(row_stock_week_desc.close_price - v_mb, 2);
        end loop;
        close cur_stock_week_desc;
      
        -- 上轨、下轨
        v_up := v_mb + 2 * sqrt(v_sum / 19);
        v_dn := v_mb - 2 * sqrt(v_sum / 19);
      
        -- 更新记录
        update stock_week
           set mb = v_mb, up = v_up, dn_ = v_dn
         where code_ = row_stock_week.code_
           and begin_date = row_stock_week.begin_date
           and end_date = row_stock_week.end_date;
      
      end loop;
      close cur_single_stock_week;
    end loop;
    close cur_stock_week_code;
    commit;
  end CAL_BOLL;

  /*-------------- 计算某一周stock_week表中的布林带，必须在计算完均线后运行 -----------------*/
  procedure CAL_BOLL_BY_DATE(p_begin_date varchar2, p_end_date varchar2) is
    -- 股票代码
    v_code varchar(50);
    -- 布林带中轨、上轨、下轨
    v_mb number;
    v_up number;
    v_dn number;
    -- 计算标准差时使用
    v_sum number := 0;
    -- 记录数量
    v_num number;
    -- 某只股票的一条交易记录
    row_stock_week      stock_week%rowtype;
    row_stock_week_desc stock_week%rowtype;
    -- 周线级别所有股票的code_
    cursor cur_stock_code is
      select distinct t.code_
        from stock_week t
       where t.begin_date >= to_date(p_begin_date, 'yyyy-mm-dd')
         and t.end_date <= to_date(p_end_date, 'yyyy-mm-dd');
    -- 某一只股票的全部交易记录，降序排列
    cursor cur_single_stock_week is
      select *
        from stock_week t
       where t.code_ = v_code
         and t.ma20 is not null
       order by t.begin_date desc;
    -- 某只股票，在某日之后的交易记录，降序排列
    cursor cur_stock_week_desc is
      select *
        from (select *
                from stock_week t
               where t.code_ = v_code
                 and t.begin_date <= to_date(p_begin_date, 'yyyy-mm-dd')
               order by t.begin_date desc)
       where rownum <= 20;
  begin
    open cur_stock_code;
    loop
      fetch cur_stock_code
        into v_code;
      exit when cur_stock_code%notfound;
    
      -- 如果交易次数不够20，则返回
      select count(*)
        into v_num
        from stock_week t
       where t.code_ = v_code
         and t.begin_date <= to_date(p_begin_date, 'yyyy-mm-dd');
      if v_num < 20 then
        continue;
      end if;
    
      /*open cur_single_stock_week;
      loop
        fetch cur_single_stock_week
          into row_stock_week;
        exit when cur_single_stock_week%notfound;*/
    
      -- 初始化
      v_up  := 0;
      v_dn  := 0;
      v_sum := 0;
    
      -- 中轨
      select avg(t1.close_price)
        into v_mb
        from (select *
                from stock_week t
               where t.code_ = v_code
                 and t.begin_date <= to_date(p_begin_date, 'yyyy-mm-dd')
               order by t.begin_date desc) t1
       where rownum <= 20;
      -- v_mb := row_stock_week.ma20;
    
      open cur_stock_week_desc;
      loop
        fetch cur_stock_week_desc
          into row_stock_week_desc;
        exit when cur_stock_week_desc%notfound;
        v_sum := v_sum + power(row_stock_week_desc.close_price - v_mb, 2);
      end loop;
      close cur_stock_week_desc;
    
      -- 上轨、下轨
      v_up := v_mb + 2 * sqrt(v_sum / 19);
      v_dn := v_mb - 2 * sqrt(v_sum / 19);
    
      -- 更新记录
      update stock_week
         set mb = v_mb, up = v_up, dn_ = v_dn
       where code_ = v_code
         and begin_date = to_date(p_begin_date, 'yyyy-mm-dd')
         and end_date = to_date(p_end_date, 'yyyy-mm-dd');
    
      /*end loop;
      close cur_single_stock_week;*/
    end loop;
    close cur_stock_code;
    commit;
  end CAL_BOLL_BY_DATE;

  ---------------------------------- 计算表STOCK_WEEK的所有Hei Kin Ashi字段 -----------------------------
  procedure CAL_STOCK_WEEK_HA is
    -- 股票代码
    v_code varchar2(10);
    -- 结束日期
    v_end_date date;
    -- 获取所有的CODE
    cursor cur_all_code is
      select distinct t.code_ code from stock_transaction_data_all t;
    -- 查询某个具体的股票的第一条交易记录
    cursor cur_fist_stock_week is
      select *
        from (select *
                from stock_week t
               where t.code_ = v_code
               order by t.end_date asc)
       where rownum <= 1;
    -- 查询某个股票（周线级别）除了最早的一条记录外的其他记录，并按升序排列
    cursor cur_later_stock_week is
      select *
        from stock_week t
       where t.code_ = v_code
         and t.end_date > v_end_date
       order by t.end_date asc;
    -- 定义表stock_week结构的游标变量
    first_stock_week stock_week%rowtype;
    later_stock_week stock_week%rowtype;
    pre_stock_week   stock_week%rowtype;
    -- 用于计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价的变量
    v_ha_open_price    number;
    v_ha_close_price   number;
    v_ha_highest_price number;
    v_ha_lowest_price  number;
  begin
    open cur_all_code;
    loop
      -- 获取每个股票的index_code字段
      fetch cur_all_code
        into v_code;
      exit when cur_all_code%notfound;
    
      -- 先计算每个股票的第一条记录
      open cur_fist_stock_week;
      fetch cur_fist_stock_week
        into first_stock_week;
      exit when cur_fist_stock_week%notfound;
    
      -- 计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价
      v_ha_open_price  := (first_stock_week.open_price +
                          first_stock_week.close_price) / 2;
      v_ha_close_price := (first_stock_week.open_price +
                          first_stock_week.close_price +
                          first_stock_week.highest_price +
                          first_stock_week.lowest_price) / 4;
      if first_stock_week.highest_price > v_ha_open_price then
        v_ha_highest_price := first_stock_week.highest_price;
      else
        v_ha_highest_price := v_ha_open_price;
      end if;
      if first_stock_week.lowest_price < v_ha_open_price then
        v_ha_lowest_price := first_stock_week.lowest_price;
      else
        v_ha_lowest_price := v_ha_open_price;
      end if;
      -- 保存hei kin ashi平均K线开盘价，收盘价，最高价和最低价
      update stock_week t
         set t.ha_week_open_price    = v_ha_open_price,
             t.ha_week_close_price   = v_ha_close_price,
             t.ha_week_highest_price = v_ha_highest_price,
             t.ha_week_lowest_price  = v_ha_lowest_price
       where t.code_ = first_stock_week.code_
         and t.end_date = first_stock_week.end_date;
      commit;
      v_end_date := first_stock_week.end_date;
      close cur_fist_stock_week;
    
      -- 再计算每个股票的其他记录
      open cur_later_stock_week;
      loop
        fetch cur_later_stock_week
          into later_stock_week;
        exit when cur_later_stock_week%notfound;
      
        -- 前一条记录
        select *
          into pre_stock_week
          from (select *
                  from stock_week t
                 where t.code_ = v_code
                   and t.end_date <= v_end_date
                 order by t.end_date desc)
         where rownum <= 1;
      
        -- 计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价
        v_ha_open_price  := (pre_stock_week.ha_week_open_price +
                            pre_stock_week.ha_week_close_price) / 2;
        v_ha_close_price := (later_stock_week.open_price +
                            later_stock_week.close_price +
                            later_stock_week.highest_price +
                            later_stock_week.lowest_price) / 4;
        if later_stock_week.highest_price > v_ha_open_price then
          v_ha_highest_price := later_stock_week.highest_price;
        else
          v_ha_highest_price := v_ha_open_price;
        end if;
        if later_stock_week.lowest_price < v_ha_open_price then
          v_ha_lowest_price := later_stock_week.lowest_price;
        else
          v_ha_lowest_price := v_ha_open_price;
        end if;
        -- 保存hei kin ashi平均K线开盘价，收盘价，最高价和最低价
        update stock_week t
           set t.ha_week_open_price    = v_ha_open_price,
               t.ha_week_close_price   = v_ha_close_price,
               t.ha_week_highest_price = v_ha_highest_price,
               t.ha_week_lowest_price  = v_ha_lowest_price
         where t.code_ = later_stock_week.code_
           and t.end_date = later_stock_week.end_date;
        commit;
      
        -- 把这次的数据留给下一次迭代使用
        v_end_date := later_stock_week.end_date;
      end loop;
      close cur_later_stock_week;
    
    end loop;
    close cur_all_code;
  end CAL_STOCK_WEEK_HA;

  /*--------------------------- 按照日期，计算表STOCK_WEEKEND的Hei Kin Ashi记录 --------------------------------*/
  procedure CAL_STOCK_WEEK_HA_BY_DATE(p_begin_date in varchar2,
                                      p_end_date   in varchar2) is
    -- 获取所有的CODE
    cursor cur_all_code is
      select distinct t.code_ code from stock_week t;
    -- 表示CODE类型的变量
    v_code varchar2(10);
  
    -- 查询某个指数在日期p_date之前的那一条记录
    cursor cur_all_stock_week is
      select *
        from (select *
                from (select *
                        from stock_week t
                       where t.code_ = v_code
                         and t.end_date <= to_date(p_end_date, 'yyyy-mm-dd')
                         and t.begin_date <=
                             to_date(p_begin_date, 'yyyy-mm-dd')
                       order by t.end_date desc)
               where rownum <= 2) t2
       where t2.end_date <>
             (select t1.end_date
                from stock_week t1
               where t1.code_ = v_code
                 and t1.end_date = to_date(p_end_date, 'yyyy-mm-dd'))
         and t2.begin_date <>
             (select t1.begin_date
                from stock_week t1
               where t1.code_ = v_code
                 and t1.begin_date = to_date(p_begin_date, 'yyyy-mm-dd'));
    -- 定义表stock_week结构的游标变量，用于接收游标cur_later_stock_week的记录
    all_stock_week stock_week%rowtype;
  
    -- 查询某个具体的指数的某一日交易记录
    cursor cur_later_stock_week is
      select *
        from stock_week t
       where t.code_ = v_code
         and t.begin_date >= to_date(p_begin_date, 'yyyy-mm-dd')
         and t.end_date <= to_date(p_end_date, 'yyyy-mm-dd');
    -- 定义表stock_week结构的游标变量，用于接收游标cur_all_stock_index_week的记录
    later_stock_week stock_week%rowtype;
  
    -- 用于计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价的变量
    v_ha_week_open_price    number;
    v_ha_week_close_price   number;
    v_ha_week_highest_price number;
    v_ha_week_lowest_price  number;
  begin
    open cur_all_code;
    loop
      -- 获取每个股票的code字段
      fetch cur_all_code
        into v_code;
      exit when cur_all_code%notfound;
    
      open cur_all_stock_week;
      open cur_later_stock_week;
      loop
        fetch cur_all_stock_week
          into all_stock_week;
        exit when cur_all_stock_week%notfound;
      
        fetch cur_later_stock_week
          into later_stock_week;
        exit when cur_later_stock_week%notfound;
      
        -- 计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价
        v_ha_week_open_price  := (all_stock_week.ha_week_open_price +
                                 all_stock_week.ha_week_close_price) / 2;
        v_ha_week_close_price := (later_stock_week.open_price +
                                 later_stock_week.close_price +
                                 later_stock_week.highest_price +
                                 later_stock_week.lowest_price) / 4;
        if later_stock_week.highest_price > v_ha_week_open_price then
          v_ha_week_highest_price := later_stock_week.highest_price;
        else
          v_ha_week_highest_price := v_ha_week_open_price;
        end if;
        if later_stock_week.lowest_price < v_ha_week_open_price then
          v_ha_week_lowest_price := later_stock_week.lowest_price;
        else
          v_ha_week_lowest_price := v_ha_week_open_price;
        end if;
        -- 保存hei kin ashi平均K线开盘价，收盘价，最高价和最低价
        update stock_week t
           set t.ha_week_open_price    = v_ha_week_open_price,
               t.ha_week_close_price   = v_ha_week_close_price,
               t.ha_week_highest_price = v_ha_week_highest_price,
               t.ha_week_lowest_price  = v_ha_week_lowest_price
         where t.code_ = later_stock_week.code_
           and t.begin_date = later_stock_week.begin_date
           and t.end_date = later_stock_week.end_date;
        commit;
      end loop;
      close cur_all_stock_week;
      close cur_later_stock_week;
    
    end loop;
    close cur_all_code;
  end CAL_STOCK_WEEK_HA_BY_DATE;

  /*------------------------------ judge if MACD of the stock weekend is the end deviation by date -----------------------------------------*/
  procedure FIND_WEEK_MACD_END_DEVIATE(p_stock_week_begin_date in varchar2,
                                       p_stock_week_end_date   in varchar2,
                                       p_rate_week_date        in varchar2,
                                       p_stock_week_result     out clob) as
    -- 定义一个stock_week%rowtype类型的变量
    row_stock_week_record stock_week%rowtype;
    -- 定义一个含有2个数值型数据的数组
    type type_array_dif_dea is varray(2) of stock_week%rowtype;
    t_array_dif_dea type_array_dif_dea := type_array_dif_dea();
    -- 定义一个含有2560个数值型数据的数组
    type type_array_gold_cross is varray(2560) of stock_week%rowtype;
    t_array_gold_cross type_array_gold_cross := type_array_gold_cross();
    -- 表示code_
    v_stock_week_code varchar2(10);
    -- 用于遍历某只股票的所有周
    v_move_stock_week_begin_date date;
    v_move_stock_week_end_date   date;
    -- 返回全部code_
    cursor cur_all_stock_week_code is
      select distinct t.code_
        from stock_week t /*where t.code_='002152'*/
       order by t.code_ asc;
    -- 获取某只股票某一周及之前所有的记录
    cursor cur_single_stock_week is
      select *
        from stock_week t
       where t.code_ = v_stock_week_code
         and t.begin_date <= to_date(p_stock_week_begin_date, 'yyyy-mm-dd')
         and t.end_date <= to_date(p_stock_week_end_date, 'yyyy-mm-dd')
       order by t.begin_date desc;
    -- 获取某只股票某一周及之前一周的记录
    cursor cur_last_two_day_stock_week is
      select *
        from (select *
                from stock_week t
               where t.code_ = v_stock_week_code
                 and t.begin_date <= v_move_stock_week_begin_date
                 and t.end_date <= v_move_stock_week_end_date
               order by t.begin_date desc)
       where rownum <= 2;
    -- 创建UTL_FILE.file_type对象，用于读写文件
    file_handle UTL_FILE.file_type;
  begin
    file_handle         := UTL_FILE.FOPEN('TXTDIR',
                                          'SELECT_WEEK_MACD_END_DEVIATION.txt',
                                          'w');
    p_stock_week_result := '';
    for i in cur_all_stock_week_code loop
      v_stock_week_code  := i.code_;
      t_array_gold_cross := type_array_gold_cross();
      for x in cur_single_stock_week loop
        v_move_stock_week_begin_date := x.begin_date;
        v_move_stock_week_end_date   := x.end_date;
        t_array_dif_dea              := type_array_dif_dea();
        for j in cur_last_two_day_stock_week loop
          row_stock_week_record.dif         := j.dif;
          row_stock_week_record.dea         := j.dea;
          row_stock_week_record.code_       := j.code_;
          row_stock_week_record.Begin_Date  := j.begin_date;
          row_stock_week_record.End_Date    := j.end_date;
          row_stock_week_record.close_price := j.close_price;
          t_array_dif_dea.extend;
          t_array_dif_dea(t_array_dif_dea.count) := row_stock_week_record;
        end loop;
        -- 判断是否是金叉。如果是，则存入数组t_array_gold_cross中
        if t_array_dif_dea.count >= 2 and t_array_dif_dea(1)
          .dif > t_array_dif_dea(1).dea and t_array_dif_dea(2)
          .dif < t_array_dif_dea(2).dea then
          t_array_gold_cross.extend;
          t_array_gold_cross(t_array_gold_cross.count) := t_array_dif_dea(1);
          if t_array_gold_cross.count > 1 then
            -- 判断是否是底背离
            if t_array_gold_cross(1)
             .close_price < t_array_gold_cross(2).close_price and
                (t_array_gold_cross(1).dif + t_array_gold_cross(1).dea) / 2 >
                (t_array_gold_cross(2).dif + t_array_gold_cross(2).dea) / 2 then
              -- 要求在阈值日期之后。阈值一般为一周的第一个交易日
              if t_array_gold_cross(1)
               .Begin_Date >= to_date(p_rate_week_date, 'yyyy-mm-dd') and t_array_gold_cross(1)
                 .End_Date >= to_date(p_rate_week_date, 'yyyy-mm-dd') then
                -- 设置返回值。注意：最后一个逗号是多余的
                p_stock_week_result := p_stock_week_result || t_array_gold_cross(1)
                                      .code_ || '&' ||
                                       to_char(t_array_gold_cross(1)
                                               .Begin_Date,
                                               'yyyymmdd') || '&' ||
                                       to_char(t_array_gold_cross(1)
                                               .End_Date,
                                               'yyyymmdd') || ',';
                /*dbms_output.put(t_array_gold_cross(1).code_);
                dbms_output.put_line('    '||t_array_gold_cross(1).Begin_Date);
                dbms_output.put_line('    '||t_array_gold_cross(1).End_Date);*/
                UTL_FILE.PUT_LINE(file_handle,
                                  t_array_gold_cross(1).code_ || '    ' || t_array_gold_cross(1)
                                  .Begin_Date || '    ' || t_array_gold_cross(1)
                                  .End_Date);
              end if;
            end if;
            goto next;
          end if;
        end if;
      end loop;
      <<next>>
      null;
    end loop;
  end FIND_WEEK_MACD_END_DEVIATE;

  ----------------------------------------- select the gold cross of kd index in weekend scale -----------------------------------------
  procedure SELECT_WEEK_KD_UP(p_begin_date              in varchar2,
                              p_end_date                in varchar2,
                              p_cross_point             in number,
                              p_stock_week_result_array out T_STOCK_WEEK_RESULT_ARRAY) as
    -- 表示某只股票的code_字段
    v_stock_code varchar2(10);
    -- 定义一个含有6360个stock_week记录的数组
    type type_array is varray(6360) of stock_week%rowtype;
    type_stock_week_record type_array := type_array();
    -- 定义T_STOCK_WEEK_RESULT对象，用于返回记录查询到的code_
    type_stock_week_result T_STOCK_WEEK_RESULT;
    -- 表示某一周的所有股票代码
    cursor cur_selected_stock_week_code is
      select t.code_
        from stock_week t
       where t.begin_date = to_date(p_begin_date, 'yyyy-mm-dd')
         and t.end_date = to_date(p_end_date, 'yyyy-mm-dd');
    --cursor 表示某只股票某一周及其之后一周的记录
    cursor cur_last_two_stock_week is
      select *
        from (select *
                from stock_week t
               where t.code_ = v_stock_code
                 and t.begin_date <= to_date(p_begin_date, 'yyyy-mm-dd')
                 and t.end_date <= to_date(p_end_date, 'yyyy-mm-dd')
               order by t.end_date desc)
       where rownum <= 2;
    -- 创建UTL_FILE.file_type对象，用于读写文件
    file_handle UTL_FILE.file_type;
  begin
    file_handle               := UTL_FILE.FOPEN('TXTDIR',
                                                'SELECT_WEEK_KD_UP.txt',
                                                'w');
    p_stock_week_result_array := T_STOCK_WEEK_RESULT_ARRAY();
  
    for i in cur_selected_stock_week_code loop
      v_stock_code := i.code_;
      for j in cur_last_two_stock_week loop
        type_stock_week_record.extend;
        type_stock_week_record(type_stock_week_record.count).k := j.k;
        type_stock_week_record(type_stock_week_record.count).d := j.d;
      end loop;
      if type_stock_week_record(1).k > type_stock_week_record(1).d then
        if type_stock_week_record(2).k < type_stock_week_record(2).d then
          if (type_stock_week_record(1)
             .k + type_stock_week_record(1).d + type_stock_week_record(2).k + type_stock_week_record(2).d) / 4 <=
             p_cross_point then
            dbms_output.put_line(i.code_);
            p_stock_week_result_array.extend;
            type_stock_week_result := T_STOCK_WEEK_RESULT(i.code_,
                                                          to_date(p_begin_date,
                                                                  'yyyy-mm-dd'),
                                                          to_date(p_end_date,
                                                                  'yyyy-mm-dd'));
            p_stock_week_result_array(p_stock_week_result_array.count) := type_stock_week_result;
            UTL_FILE.PUT_LINE(file_handle, i.code_);
          end if;
        end if;
      end if;
      type_stock_week_record := type_array();
    end loop;
  end SELECT_WEEK_KD_UP;

end PKG_WEEK;